var express = require('express')
var router = express.Router()

const { getALL, getById, exec } = require('../db')

//获取所有文章:GET/articles
router.get('/', async (req, res) => {
  //解析请求参数
  const { page = 1, size = 5 } = req.query
  let sql = `select count(*) as total from articles`
  let { total } = await getById(sql)
  //定义偏移量
  let offset = (page - 1) * size
  //操作数据库
  sql = `select articles.id,title,updated_time,abstract,content,pageviews,image_address,category_name from articles LEFT OUTER JOIN categories ON categories.id = articles.category_id  limit ${offset},${size}`
  const data = await getALL(sql)
  res.send({
    code: 0,
    message: '获取所有文章成功',
    result: {
      total,
      data,
    },
  })
})

// 获取现有文章分类:GET/articles/nowcategory
router.get('/nowcategory', async (req, res) => {
  let sql = `select DISTINCT category_name from articles LEFT OUTER JOIN categories ON categories.id = articles.category_id `
  const data = await getALL(sql)
  res.send({
    code: 0,
    message: '获取现有文章分类成功',
    result: { data },
  })
})

// 分类查询
router.get('/select', async (req, res) => {
  //解析请求参数
  // console.log(req.query)
  const { page = 1, size = 10, category_name } = req.query
  let sql = `select count(*) as total from articles LEFT OUTER JOIN categories ON categories.id = articles.category_id  where category_name ='${category_name}'`
  // console.log(sql)
  let { total } = await getById(sql)
  //定义偏移量
  let offset = (page - 1) * size
  //操作数据库
  sql = `select author,articles.id,title,updated_time,pageviews,abstract,content,category_name ,image_address from articles LEFT OUTER JOIN categories ON categories.id = articles.category_id  where category_name = '${category_name}' limit ${offset},${size}`
  // console.log(sql)
  const data = await getALL(sql)
  res.send({
    code: 0,
    message: '获取分类文章成功',
    result: {
      total,
      data,
    },
  })
})

//修改点击量:POST/
router.put('/', async (req, res) => {
  //解析参数
  const { id, pageviews } = req.body
  //操作数据库
  let sql = `update articles set pageviews='${pageviews}' where id = ${id}`
  const data = await exec(sql)
  let sql1 = `select pageviews from articles  where id = ${id}`
  const pageviewsdata = await getById(sql1)
  res.send({
    code: 0,
    message: '修改点击量成功',
    result: {
      data,
      pageviewsdata,
    },
  })
})

module.exports = router
